
<!DOCTYPE html>
<html lang="zh-CN" class="loading">
<head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
    <meta name="viewport" content="width=device-width, minimum-scale=1.0, maximum-scale=1.0, user-scalable=no">
    <title>Mysql解惑01 - 李奇李琦李颀</title>
    <meta name="apple-mobile-web-app-capable" content="yes" />
    <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
    <meta name="google" content="notranslate" />
    <meta name="keywords" content="TriDiamond Obsidian,"> 
    <meta name="description" content="起因在leetcode刷题的时候遇到的Mysql题目 隔段时间回头看还是不能自己写出来 重新整理了下思路
排序问题原题目: https://leetcode-cn.com/problems/rank,"> 
    <meta name="author" content="李奇李琦李颀"> 
    <link rel="alternative" href="atom.xml" title="李奇李琦李颀" type="application/atom+xml"> 
    <link rel="icon" href="/img/favicon.png"> 
    <link href="https://fonts.loli.net/css?family=Roboto+Mono|Rubik&display=swap" rel="stylesheet">
    
<link rel="stylesheet" href="//at.alicdn.com/t/font_1429596_nzgqgvnmkjb.css">

    
<link rel="stylesheet" href="//cdn.bootcss.com/animate.css/3.7.2/animate.min.css">

    
<link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/social-share.js/1.0.16/css/share.min.css">

    
<link rel="stylesheet" href="//cdn.bootcss.com/codemirror/5.48.4/codemirror.min.css">

    
<link rel="stylesheet" href="//cdn.bootcss.com/codemirror/5.48.4/theme/dracula.css">

    
<link rel="stylesheet" href="/css/obsidian.css">

    
<link rel="stylesheet" href="/css/ball-atom.min.css">

<meta name="generator" content="Hexo 4.2.0"></head>


<body class="loading">
    <div class="loader">
        <div class="la-ball-atom la-2x">
            <div></div>
            <div></div>
            <div></div>
            <div></div>
        </div>
    </div>
    <span id="config-title" style="display:none">李奇李琦李颀</span>
    <div id="loader"></div>
    <div id="single">
    <div class="scrollbar gradient-bg-rev"></div>
<div id="top" style="display: block;">
    <div class="bar" style="width: 0;"></div>
    <div class="navigation animated fadeIn fast delay-1s">
        <img id="home-icon" class="icon-home" src="/img/avater-r.png" alt="" data-url="http://li423.gitee.io">
        <div id="play-icon" title="Play/Pause" class="iconfont icon-play"></div>
        <h3 class="subtitle">Mysql解惑01</h3>
        <div class="social">
            <!--        <div class="like-icon">-->
            <!--            <a href="javascript:;" class="likeThis active"><span class="icon-like"></span><span class="count">76</span></a>-->
            <!--        </div>-->
            <div>
                <div class="share">
                    
                        <a href="javascript:;" class="iconfont icon-share1"></a>
                        <div class="share-component-cc" data-disabled="facebook,douban,linkedin,diandian,tencent,google"></div>
                    
                </div>
            </div>
        </div>
    </div>
</div>

    <div class="section">
        <div class=article-header-wrapper>
    <div class="article-header">
        <div class="article-cover animated fadeIn" style="
            animation-delay: 600ms;
            animation-duration: 1.2s;
            background-image:
                radial-gradient(ellipse closest-side, rgba(0, 0, 0, 0.65), #100e17),
                url(/img/cover.jpg);">
        </div>
        <div class="else">
            <p class="animated fadeInDown">
                
                <a href="/categories/Mysql"><b>「
                    </b>MYSQL<b> 」</b></a>
                
                六月 08, 2020
            </p>
            <h3 class="post-title animated fadeInDown"><a href="/2020/06/08/Mysql%E8%A7%A3%E6%83%9101/" title="Mysql解惑01" class="">Mysql解惑01</a>
            </h3>
            
            <p class="post-count animated fadeInDown">
                
                <span>
                    <b class="iconfont icon-text2"></b> <i>文章字数</i>
                    2.5k
                </span>
                
                
                <span>
                    <b class="iconfont icon-timer__s"></b> <i>阅读约需</i>
                    2 mins.
                </span>
                
                
                
                <span id="busuanzi_container_page_pv">
                    <b class="iconfont icon-read"></b> <i>阅读次数</i>
                    <span id="busuanzi_value_page_pv">0</span>
                </span>
                
            </p>
            
            
            <ul class="animated fadeInDown post-tags-list" itemprop="keywords"><li class="animated fadeInDown post-tags-list-item"><a class="animated fadeInDown post-tags-list-link" href="/tags/Mysql/" rel="tag">Mysql</a></li></ul>
            
        </div>
    </div>
</div>

<div class="screen-gradient-after">
    <div class="screen-gradient-content">
        <div class="screen-gradient-content-inside">
            <div class="bold-underline-links screen-gradient-sponsor">
                <p>
                    <span class="animated fadeIn delay-1s"></span>
                </p>
            </div>
        </div>
    </div>
</div>

<div class="article">
    <div class='main'>
        <div class="content markdown animated fadeIn">
            <h2 id="起因"><a href="#起因" class="headerlink" title="起因"></a>起因</h2><p>在leetcode刷题的时候遇到的Mysql题目 隔段时间回头看还是不能自己写出来 重新整理了下思路</p>
<h2 id="排序问题"><a href="#排序问题" class="headerlink" title="排序问题"></a>排序问题</h2><p>原题目: <a href="https://leetcode-cn.com/problems/rank-scores/" target="_blank" rel="noopener">https://leetcode-cn.com/problems/rank-scores/</a></p>
<p>首先如果不考虑名次是不是连续的问题 题目只需要根据分数进行排名</p>
<p>那么可以使用自连接 查询比自己分数低的人数</p>
<pre><code class="mysql">select s1.Score, count(s2.Score) as &apos;Rank&apos;
from Scores s1, Scores s2
where s1.Score &lt;= s2.Score
group by s1.Id
order by &apos;Rank&apos;</code></pre>
<pre><code>注: Mysql8版本支持rank函数 属内置函数 需加单引号</code></pre><img src="https://i.loli.net/2020/06/08/IwROo2NYXHkbe6c.png" alt="sql01" style="zoom:150%;" />

<p>现在得到的结果和预计的并不一样 为什么没有第一名呢</p>
<p>依据数据来看 Score = 4 应该为第一名 但是Score = 4的数据有两条 判断的时候需要去重</p>
<pre><code class="mysql">select s1.Score, count(s2.Score) as &apos;Rank&apos;
from Scores s1, Scores s2
where s1.Score &lt; s2.Score
group by s1.Id
order by s1.Score desc</code></pre>
<pre><code>注: Mysql8版本rank属于内置函数依据s1.Score倒序排列</code></pre><img src="https://i.loli.net/2020/06/08/x5RDPdaEnKuzWrB.png" alt="sql02.png" style="zoom:150%;" />

<p>这就得到了最终的结果  名次连续问题也不用特别处理. Over.</p>
<p>重新刷题的时候看到评论区里面有一些不一样的回答</p>
<p>挑选两个学习一下</p>
<pre><code class="mysql">SELECT Score, dense_rank() over(order by Score desc) as &apos;Rank&apos;
FROM Scores</code></pre>
<p>第一次看到的时候内心OS(居然还有这种函数 是我见识太少 -_-  )</p>
<p>随后查了 dense_rank(), over()函数</p>
<h2 id="Rank-函数详解"><a href="#Rank-函数详解" class="headerlink" title="Rank()函数详解"></a>Rank()函数详解</h2><p>dense_rank(), rank()和 row_number()都是排序函数</p>
<p>不过排序方式不一样</p>
<p>rank() -&gt; 跳跃排序 1134这种</p>
<p>dense_rank() -&gt; 连续排序 1123这种</p>
<p>row_number() -&gt; 没有重复值的排序(记录相等也是不重复的) 自然排序</p>
<p>ntile(group_num) -&gt; 将查询数据分成group_num数目的组数 </p>
<p>over()函数不能单独使用要和上面的分析函数一起使用</p>
<p>参数: over(partition by columnname1 order by columnname2)</p>
<p>含义: 按(colomnname1指定的字段进行分组 或者 按字段columnname2排序) </p>
<pre><code class="mysql">select s1.score,
(select count(*)+1
from (select distinct score from Scores) s2
where s2.score&gt;s1.score) as &apos;Rank&apos;
from Scores s1
order by s1.Score desc</code></pre>
<img src="https://i.loli.net/2020/06/08/qAn12wVF8tkaidB.png" alt="sql03.png" style="zoom:150%;" />

<p>这个版本和最初的思路是一致的 不过效率比较高</p>
<p>在不加where限制条件的情况下, count(*)与count(col)基本可以认为是等价的</p>
<p>但是在有where限制条件的情况下count(*)会比count(col)快非常多</p>
<h2 id="收工-回家吃饭"><a href="#收工-回家吃饭" class="headerlink" title="收工 回家吃饭~"></a>收工 回家吃饭~</h2>
            <!--[if lt IE 9]><script>document.createElement('audio');</script><![endif]-->
            <audio id="audio" loop="1" preload="auto" controls="controls"
                data-autoplay="false">
                <source type="audio/mpeg" src="">
            </audio>
            
            <ul id="audio-list" style="display:none">
                
                
                <li title='0' data-url='/statics/Battles and Wastelands.mp3'></li>
                
                    
            </ul>
            
            
            
<div id="giteementDiv"></div>
<link rel="stylesheet" href="https://giteement.oss-cn-beijing.aliyuncs.com/default.css">
<script src="https://giteement.oss-cn-beijing.aliyuncs.com/giteement.browser.js"></script>
<script>
var giteement = new Giteement({
  id: '20200608092421',
  owner: 'li423',
  repo: 'li423',
  backcall_uri: 'https://li423.gitee.io',
  oauth_uri: 'https://cors-anywhere.herokuapp.com/https://gitee.com/oauth/token ',
  oauth: {
    client_id: 'e7fe221c57468d5f1909606a7d96670cfb9afb9d09b5c6b940167d07922f9d40',
    client_secret: 'bd5b3e7e215929312ac98665ec041bcc3a25ec35221393ca32b4c22bce1d1963'
  },
})
giteement.render('giteementDiv')
</script>


            
            
        </div>
        <div class="sidebar">
            <div class="box animated fadeInRight">
                <div class="subbox">
                    <img src="/img/avater.jpg" height=300 width=300></img>
                    <p>李奇李琦李颀</p>
                    <span>Think like an artist, develop like an artisan</span>
                    <dl>
                        <dd><a href="https://gitee.com/li423" target="_blank"><span
                                    class=" iconfont icon-github"></span></a></dd>
                        <dd><a href="" target="_blank"><span
                                    class=" iconfont icon-twitter"></span></a></dd>
                        <dd><a href="" target="_blank"><span
                                    class=" iconfont icon-stack-overflow"></span></a></dd>
                    </dl>
                </div>
                <ul>
                    <li><a href="/">2 <p>文章</p></a></li>
                    <li><a href="/categories">2 <p>分类</p></a></li>
                    <li><a href="/tags">2 <p>标签</p></a></li>
                </ul>
            </div>
            
            
            
            <div class="box sticky animated fadeInRight faster">
                <div id="toc" class="subbox">
                    <h4>目录</h4>
                    <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#起因"><span class="toc-number">1.</span> <span class="toc-text">起因</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#排序问题"><span class="toc-number">2.</span> <span class="toc-text">排序问题</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#Rank-函数详解"><span class="toc-number">3.</span> <span class="toc-text">Rank()函数详解</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#收工-回家吃饭"><span class="toc-number">4.</span> <span class="toc-text">收工 回家吃饭~</span></a></li></ol>
                </div>
            </div>
            
            
        </div>
    </div>
</div>

    </div>
</div>
    <div id="back-to-top" class="animated fadeIn faster">
        <div class="flow"></div>
        <span class="percentage animated fadeIn faster">0%</span>
        <span class="iconfont icon-top02 animated fadeIn faster"></span>
    </div>
</body>
<footer>
    <p class="copyright" id="copyright">
        &copy; 2020
        <span class="gradient-text">
            李奇李琦李颀
        </span>.
        Powered by <a href="http://hexo.io/" title="Hexo" target="_blank" rel="noopener">Hexo</a>
        Theme
        <span class="gradient-text">
            <a href="https://github.com/TriDiamond/hexo-theme-obsidian" title="Obsidian" target="_blank" rel="noopener">Obsidian</a>
        </span>
        <small><a href="https://github.com/TriDiamond/hexo-theme-obsidian/blob/master/CHANGELOG.md" title="v1.4.3" target="_blank" rel="noopener">v1.4.3</a></small>
    </p>
</footer>

<script type="text/javascript" src="https://cdn.bootcss.com/mathjax/2.7.6/MathJax.js?config=TeX-AMS-MML_HTMLorMML">
</script>
<script>
  MathJax.Hub.Config({
    "HTML-CSS": {
      preferredFont: "TeX",
      availableFonts: ["STIX", "TeX"],
      linebreaks: {
        automatic: true
      },
      EqnChunk: (MathJax.Hub.Browser.isMobile ? 10 : 50)
    },
    tex2jax: {
      inlineMath: [
        ["$", "$"],
        ["\\(", "\\)"]
      ],
      processEscapes: true,
      ignoreClass: "tex2jax_ignore|dno",
      skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
    },
    TeX: {
      noUndefined: {
        attributes: {
          mathcolor: "red",
          mathbackground: "#FFEEEE",
          mathsize: "90%"
        }
      },
      Macros: {
        href: "{}"
      }
    },
    messageStyle: "none"
  });
</script>
<script>
  function initialMathJax() {
    MathJax.Hub.Queue(function () {
      var all = MathJax.Hub.getAllJax(),
        i;
      // console.log(all);
      for (i = 0; i < all.length; i += 1) {
        console.log(all[i].SourceElement().parentNode)
        all[i].SourceElement().parentNode.className += ' has-jax';
      }
    });
  }

  function reprocessMathJax() {
    if (typeof MathJax !== 'undefined') {
      MathJax.Hub.Queue(["Typeset", MathJax.Hub]);
    }
  }
</script>



    
<link rel="stylesheet" href="//cdn.bootcss.com/gitalk/1.5.0/gitalk.min.css">

    
<script src="//cdn.bootcss.com/gitalk/1.5.0/gitalk.min.js"></script>



<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="/js/plugin.js"></script>
<script src="/js/obsidian.js"></script>
<script src="/js/jquery.truncate.js"></script>
<script src="/js/search.js"></script>


<script src="//cdn.bootcss.com/typed.js/2.0.10/typed.min.js"></script>


<script src="//cdn.bootcss.com/blueimp-md5/2.12.0/js/md5.min.js"></script>


<script src="//cdnjs.cloudflare.com/ajax/libs/social-share.js/1.0.16/js/social-share.min.js"></script>


<script src="https://cdn.bootcss.com/codemirror/5.48.4/codemirror.min.js"></script>

    
<script src="//cdn.bootcss.com/codemirror/5.48.4/mode/javascript/javascript.min.js"></script>


    
<script src="//cdn.bootcss.com/codemirror/5.48.4/mode/css/css.min.js"></script>


    
<script src="//cdn.bootcss.com/codemirror/5.48.4/mode/xml/xml.min.js"></script>


    
<script src="//cdn.bootcss.com/codemirror/5.48.4/mode/htmlmixed/htmlmixed.min.js"></script>


    
<script src="//cdn.bootcss.com/codemirror/5.48.4/mode/clike/clike.min.js"></script>


    
<script src="//cdn.bootcss.com/codemirror/5.48.4/mode/php/php.min.js"></script>


    
<script src="//cdn.bootcss.com/codemirror/5.48.4/mode/shell/shell.min.js"></script>


    
<script src="//cdn.bootcss.com/codemirror/5.48.4/mode/python/python.min.js"></script>




    
<script src="/js/busuanzi.min.js"></script>

    <script>
        $(document).ready(function () {
            if ($('span[id^="busuanzi_"]').length) {
                initialBusuanzi();
            }
        });
    </script>



<link rel="stylesheet" href="//cdn.bootcss.com/photoswipe/4.1.3/photoswipe.min.css">
<link rel="stylesheet" href="//cdn.bootcss.com/photoswipe/4.1.3/default-skin/default-skin.min.css">


<script src="//cdn.bootcss.com/photoswipe/4.1.3/photoswipe.min.js"></script>
<script src="//cdn.bootcss.com/photoswipe/4.1.3/photoswipe-ui-default.min.js"></script>


<!-- Root element of PhotoSwipe. Must have class pswp. -->
<div class="pswp" tabindex="-1" role="dialog" aria-hidden="true">
    <!-- Background of PhotoSwipe. 
         It's a separate element as animating opacity is faster than rgba(). -->
    <div class="pswp__bg"></div>
    <!-- Slides wrapper with overflow:hidden. -->
    <div class="pswp__scroll-wrap">
        <!-- Container that holds slides. 
            PhotoSwipe keeps only 3 of them in the DOM to save memory.
            Don't modify these 3 pswp__item elements, data is added later on. -->
        <div class="pswp__container">
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
        </div>
        <!-- Default (PhotoSwipeUI_Default) interface on top of sliding area. Can be changed. -->
        <div class="pswp__ui pswp__ui--hidden">
            <div class="pswp__top-bar">
                <!--  Controls are self-explanatory. Order can be changed. -->
                <div class="pswp__counter"></div>
                <button class="pswp__button pswp__button--close" title="Close (Esc)"></button>
                <button class="pswp__button pswp__button--share" title="Share"></button>
                <button class="pswp__button pswp__button--fs" title="Toggle fullscreen"></button>
                <button class="pswp__button pswp__button--zoom" title="Zoom in/out"></button>
                <!-- Preloader demo http://codepen.io/dimsemenov/pen/yyBWoR -->
                <!-- element will get class pswp__preloader--active when preloader is running -->
                <div class="pswp__preloader">
                    <div class="pswp__preloader__icn">
                      <div class="pswp__preloader__cut">
                        <div class="pswp__preloader__donut"></div>
                      </div>
                    </div>
                </div>
            </div>
            <div class="pswp__share-modal pswp__share-modal--hidden pswp__single-tap">
                <div class="pswp__share-tooltip"></div> 
            </div>
            <button class="pswp__button pswp__button--arrow--left" title="Previous (arrow left)">
            </button>
            <button class="pswp__button pswp__button--arrow--right" title="Next (arrow right)">
            </button>
            <div class="pswp__caption">
                <div class="pswp__caption__center"></div>
            </div>
        </div>
    </div>
</div>



    <!-- Global site tag (gtag.js) - Google Analytics -->
    <script async src="//www.googletagmanager.com/gtag/js?id=UA-149874671-1"></script>
    <script>
        window.dataLayer = window.dataLayer || [];
        function gtag(){dataLayer.push(arguments);}
        gtag('js', new Date());

        gtag('config', 'UA-149874671-1');
    </script>





<script>
    function initialTyped () {
        var typedTextEl = $('.typed-text');
        if (typedTextEl && typedTextEl.length > 0) {
            var typed = new Typed('.typed-text', {
                strings: ["Think like an artist, develop like an artisan", "艺术家思维去思考问题，工匠创造精神去开发"],
                typeSpeed: 90,
                loop: true,
                loopCount: Infinity,
                backSpeed: 20,
            });
        }
    }

    if ($('.article-header') && $('.article-header').length) {
        $(document).ready(function () {
            initialTyped();
        });
    }
</script>




</html>
